Introduction

I'm Shiwani from the Data Analytics Division.
This time, i tried using Structure Parser to converts input data into a user-defined structure format based on an intelligent structure model.
The structure model will read data from a table embedded in an actual Microsoft Word document. It parses the data while ignoring the text and formatting characters in the Word document.

Objective

• Create a mapping using Structure Parser transformation.

Prerequisite

The following preparations are assumed to be ready in advance:

• The IICS Secure Agent and VPC have been configured.
• Click weekly_sales.txt and download the sample file.
• Create a .txt file with the name parser_input.txt and the path of the weekly_sales.docx file as shown in the example below:

Path
\home\IICSLabFiles\SrcFiles\weekly_sales.docx

• Create a connecter to establish connection with the source files (parser_input.txt, weekly_sales.docx ).
• Create a connecter to establish connection with the target file.
  • Click WEEKLY_SALES_BY_STORE.txt and download the target file.
• Create an Intelligent Structure Model with the name WEEKLY_SALES_WORD_DOC , refer to this link to article.
  

Create Mapping

• Create Mapping with the name m_02_PARSER_INTEL_STRUCTURE.

Add Source Transformation

• Click Source transformation and set Name to WEEKLY_SALES_DOC.
  

• Select Source file connecter from Connection. here i have created connection with the name FF_Source_2. Select parser_input.txt from object.
  

Add Structure Parser Transformation

• Add Structure Parser Transformation on the link between
  WEEKLY_SALES_DOC and Target transformations.

• Select the previously created WEEKLY_SALES_WORD_DOC structure mode.

• The Structure Parser transformation output is Relational, JSON, XML, Avro,
  Parquet, and ORC format. This time, keep the output format as
  Relational.

• Link WEEKLY_SALES_DOC to StructureParser.

• Map the incoming fields to the structure parser input fields.

Add an Expression Transformation

• Add Expression Transformation between StructureParser and Target
  transformations.
• Link StructureParser with Expression transformation on the mapping.
• Select element and click OK.

• Click Expression transformation and add the following properties details.

  • General :

    • Enter the Name as CALC_AVG_TRANS_REV.
      

  • Expression :

    • To add a new expression, click the plus icon.
    • Enter the details as shown in table below.

    Field Type	Name	Type	Precision	Scale
    Output Field	AVG_TRANS_REV	bigint	19	0

    

    • To add an expression for AVG_TRANS_REV, click Configure and enter the following expression.

    TO_INTEGER(Revenue)/TO_INTEGER(Transactions)

    

• Link the CALC_AVG_TRANS_AVG to the Target.

• Click the Target transformation and enter the properties details.

  • General
    • Enter the name as WEEKLY_SALES_BY_STORE.
      
    • Click Target. From the Connection drop-down, select your target database connection.
    • Select the Target Object as WEEKLY_SALES_BY_STORE.
      
    • Click Field Mapping and Match the fields. Finally Save and Run the mapping.

Results

• Click My Jobs and check the execution status of the mapping. When the task completes, the status changes to Success.
  

Conclusion

I have successfully developed and tested the mapping using Structure Parser transformation in IDMC.
Structure Parser uses an intelligent structure model to convert incoming data to a user-defined structure format. This Structure Parser transformation is useful for analyzing log files, clickstreams, XML and JSON files, Word tables, and other unstructured or semi-structured data types.